#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
预览需要清理的无效记录
执行前请仔细查看这些记录，确认是否需要删除
"""

import pymysql
import sys
import os

# 添加父目录到路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from crawler.config.settings import DATABASE_CONFIG

def preview_invalid_records():
    conn = pymysql.connect(**DATABASE_CONFIG)
    cursor = conn.cursor()
    
    lottery_types = {
        'ssq': '双色球',
        'fc3d': '福彩3D',
        'qlc': '七乐彩',
        'kl8': '快乐8'
    }
    
    print("=" * 100)
    print("预览需要清理的无效记录")
    print("=" * 100)
    print("\n判断规则：期号中的年份必须与开奖日期的年份一致")
    print("  - 7位期号（如2025123）：前4位是年份")
    print("  - 5位期号（如25123）：前2位是年份后2位")
    print()
    
    total_invalid = 0
    
    for lottery_type, name in lottery_types.items():
        print(f"\n{'=' * 100}")
        print(f"【{name} ({lottery_type})】")
        print('=' * 100)
        
        # 查询不匹配的记录
        cursor.execute("""
            SELECT 
                id,
                period,
                draw_date,
                YEAR(draw_date) as draw_year,
                CASE 
                    WHEN period >= 2000000 THEN CAST(SUBSTRING(CAST(period AS CHAR), 1, 4) AS UNSIGNED)
                    WHEN period >= 100000 THEN CAST(CONCAT('20', SUBSTRING(CAST(period AS CHAR), 1, 2)) AS UNSIGNED)
                    WHEN period >= 10000 THEN CAST(CONCAT('20', SUBSTRING(CAST(period AS CHAR), 1, 2)) AS UNSIGNED)
                    ELSE NULL
                END as period_year,
                all_numbers
            FROM lottery_results
            WHERE lottery_type = %s
            AND (
                (period >= 2000000 AND CAST(SUBSTRING(CAST(period AS CHAR), 1, 4) AS UNSIGNED) != YEAR(draw_date))
                OR (period >= 100000 AND period < 2000000 AND CAST(CONCAT('20', SUBSTRING(CAST(period AS CHAR), 1, 2)) AS UNSIGNED) != YEAR(draw_date))
            )
            ORDER BY draw_date DESC, period DESC
        """, (lottery_type,))
        
        results = cursor.fetchall()
        
        if results:
            print(f"发现 {len(results)} 条无效记录：\n")
            print(f"{'ID':<10} {'期号':<12} {'开奖日期':<15} {'日期年份':<10} {'期号年份':<10} {'开奖号码'}")
            print("-" * 100)
            
            for record in results:
                id_val, period, draw_date, draw_year, period_year, all_numbers = record
                # 简化显示号码
                import json
                try:
                    numbers = json.loads(all_numbers) if isinstance(all_numbers, str) else all_numbers
                    if numbers and len(numbers) > 0:
                        display_numbers = ' '.join(numbers[0].split()[:5]) + '...'
                    else:
                        display_numbers = 'N/A'
                except:
                    display_numbers = 'N/A'
                
                print(f"{id_val:<10} {period:<12} {str(draw_date):<15} {draw_year:<10} {period_year or 'NULL':<10} {display_numbers}")
            
            total_invalid += len(results)
        else:
            print("✅ 无无效记录")
    
    print(f"\n{'=' * 100}")
    print(f"总计需要清理的记录数: {total_invalid}")
    print('=' * 100)
    
    cursor.close()
    conn.close()
    
    return total_invalid

if __name__ == '__main__':
    total = preview_invalid_records()
    
    if total > 0:
        print(f"\n⚠️  发现 {total} 条无效记录")
        print("下一步：")
        print("  1. 运行 02_backup_database.py 备份数据库")
        print("  2. 运行 03_clean_invalid_records.py 清理无效记录")
        print("  3. 运行 04_recalculate_omission.py 重新计算遗漏")
    else:
        print("\n✅ 数据库中没有无效记录，无需清理")

